import java.awt.*;

import javax.swing.*;

import java.sql.Connection;
import java.sql.DatabaseMetaData;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.logging.Level;
import java.util.logging.Logger;

public class TaskDatabase {
	
	private JFrame frame = new JFrame("Library Booking System");// create
	static JFrame frame2 = new JFrame("Search | Add book to cart");// create
	static JFrame frame3 = new JFrame("Receipt");// create
	static JFrame frame4 = new JFrame("Log In");// create
	
	static JLabel searchName = new JLabel(""); 
	static JButton submit = new JButton("Submit");
	static JButton add = new JButton("Add");
	static JButton ok = new JButton("OK");
	static JTextField libraryCard = new JTextField("Enter Library Card Number");
	static JButton search = new JButton("Search");
	public static JTextField isbn = new JTextField("Enter the ISBN");
	static JButton book = new JButton("Book");
	//static JLabel dbName = new JLabel("Welcome");
	
	static JTextField dbName = new JTextField ("Enter the name of the database");
	static JButton submitDb = new JButton("Submit");
	
	static JTextField rootPw = new JTextField ("Enter the root password");
	static JButton submitPw = new JButton("Submit");
	static String password = ""; 
	
	private JPanel pnlSearch = new JPanel();
	static JPanel pnlSearchN = new JPanel();
	private JPanel pnlSearchS = new JPanel();
	private JPanel pnW = new JPanel(new GridLayout(2,0,0,0));
	private JPanel pnlS = new JPanel();
	private JPanel pnlMain = new JPanel();
	
	static JPanel pnlR = new JPanel();
	static JPanel pnlRS = new JPanel();
	
	static String input = "";
	Connection con;
	static Connection con2;

	TaskDatabase(){
		
	submit.addActionListener(new ButtonListener()); // JButton Submit
	submit.setActionCommand("submit");
	
	search.addActionListener(new ButtonListener()); // JButton Search
	search.setActionCommand("search");
	search.setEnabled(false);
	book.addActionListener(new ButtonListener()); // JButton Book
	book.setActionCommand("book");
	book.setEnabled(false);
	add.addActionListener(new ButtonListener()); // JButton Add
	add.setActionCommand("add");
	add.setEnabled(false);	
	searchName.setVisible(true);
	
	submitPw.addActionListener(new ButtonListener()); // JButton Search
	submitPw.setActionCommand("submitPw");
	
	submitDb.addActionListener(new ButtonListener()); // JButton Search
	submitDb.setActionCommand("submitDb");
	
	 
	//submit.setEnabled(false);
	//libraryCard.setEnabled(false);
	//submitDb.setEnabled(false);
	//dbName.setEnabled(false); 
	//isbn.setEnabled(false);
	
	
	//pnW.add(rootPw);
	//pnW.add(submitPw); 
	//pnW.add(dbName); 
	//pnW.add(submitDb);  
	pnW.add(libraryCard);
	pnW.add(submit);
	pnW.add(isbn);
	pnW.add(search);
	pnlS.add(book);
	pnlR.add(pnlRS, BorderLayout.SOUTH); 
	
	pnlSearchS.add(add); 
	pnlSearch.add(pnlSearchN, BorderLayout.NORTH);
	pnlSearch.add(pnlSearchS, BorderLayout.SOUTH);
	pnlMain.add(pnW, BorderLayout.NORTH);
	pnlMain.add(pnlS, BorderLayout.SOUTH);
		
	frame.getContentPane().setLayout(new BorderLayout());
	frame.getContentPane().add(pnlMain, BorderLayout.CENTER);
	frame2.getContentPane().setLayout(new BorderLayout());
	frame2.getContentPane().add(pnlSearch, BorderLayout.CENTER); 
	frame3.getContentPane().setLayout(new BorderLayout());
	frame3.getContentPane().add(pnlR, BorderLayout.CENTER);
		
}

public void launchFrame() {
	// Display Frame
	frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
	frame.setSize(400,150);
	frame.setVisible(true);
	frame.setLocation(300, 200);
	frame2.setSize(400,150);
	frame2.setLocation(300, 200);
	frame3.setSize(400,150);
	frame3.setLocation(700, 200);

}

	public void connectRootDatabase() throws SQLException{
		 // Connection to the root database. 
        
		try{
			Class.forName("org.gjt.mm.mysql.Driver").newInstance();
			con = DriverManager.getConnection("jdbc:mysql://localhost/mysql", "root", TaskDatabase.password);
		    System.out.println("Connected");
		
		 } catch (Exception e) {
		      e.printStackTrace();
		 }
		    
	}
	
	
public void showAllDatabases() throws SQLException{
		// Method that displays all the databases in the root directory. 
		Statement stmt = con.createStatement(); 
        ResultSet rs = stmt.executeQuery("SHOW DATABASES ");
        while ( rs.next() ) {
        	String databaseName = rs.getString("Database");
        	System.out.println(databaseName); 
        }
		
	} // end showAllDatabases
	
	

public void createDatabase(String input) throws SQLException{

	try{
		
    Statement stmt6 = con.createStatement();
    stmt6.executeUpdate("CREATE DATABASE "+input);
    con.close(); // Close the root database after the database has been created in the root  
	}catch(Exception e){
		System.out.println("error"); 
	}
    
   
	}

public void connectNewDatabase(String name) throws SQLException{
    // Con2 is the new connection to the database created.
	try{
		Class.forName("org.gjt.mm.mysql.Driver").newInstance();
		 con2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+name+"", "root", TaskDatabase.password);
	    System.out.println("Connected to the " +name+ " database");
				
	 } catch (Exception e) {
	      e.printStackTrace();
	 }
	    
}
	
	
public void createTables(String input) throws SQLException, InstantiationException, IllegalAccessException, ClassNotFoundException{
	// Takes a String as a parameter which is the name of the new database the user specified. 
	System.out.println("here");
	try{
		
		 con2 = DriverManager.getConnection("jdbc:mysql://localhost:3306/"+input+"", "root", TaskDatabase.password);
	
	} catch (Exception e) {
	
	      e.printStackTrace();
	 }
			
	Statement stmt = con2.createStatement();
	
	try{
	// Open connection with Con2
	// Creating the tables in the database selected. 	
	stmt.executeUpdate("CREATE  TABLE IF NOT EXISTS `"+input+"`.`borrow` (`LibraryCardNumber` INT NOT NULL ,`Name` VARCHAR(70) NULL ,`Street` VARCHAR(70) NULL ,`ZipCode` INT NULL ,`Town` " +
			"VARCHAR(45) NULL ,PRIMARY KEY (`LibraryCardNumber`) );");
	
	stmt.executeUpdate("CREATE  TABLE IF NOT EXISTS `"+input+"`.`dateBorrow` (`iddateBorrow` INT NOT NULL AUTO_INCREMENT ," +
			"`ISBN` VARCHAR(45) NULL ,`BorrowDate` DATE NULL ,`ReturnDate` DATE NULL ," +
			"`LibraryCardNumber` INT NULL ,PRIMARY KEY (`iddateBorrow`) );");
				
		stmt.executeUpdate(" CREATE  TABLE `"+input+"`.`books` (`ISBN` VARCHAR(45) NOT NULL ," +
				"`CopyNumber` INT NULL ,`Title` VARCHAR(70) NULL ,`Author` VARCHAR(45) NULL ,`Publisher` VARCHAR(45) NULL ," +
				"`Year` INT NULL ,`Statictics` INT NULL ,PRIMARY KEY (`ISBN`) );");
	
	}
	catch(SQLException e){
		System.out.println(e);
	}
	
	
	
	System.out.println("Comepleted created the tables");
	
	
} // end create tables

public boolean doesExist(String input) throws SQLException{
	// Takes the users input as a parameter
	// Queries the localhost for all the databases that exists in the root
	// Searched for a match, returning boolean 
	
	Statement stmt = con.createStatement(); 
    ResultSet rs = stmt.executeQuery("SHOW DATABASES ");
    while ( rs.next() ) {
    	String databaseName = rs.getString("Database");
    	
    	if(databaseName.equals(input)){
    		return true;
    		 
    	}
    	              
    }		
	return false; 
			 
} // end doesExist



	public static void main (String [] args) throws Exception{
		
		// User inputs the name of the database.
		// If it exists, the database connection is opened
		// If the databse does not exist, a new one is created. 
		password = JOptionPane.showInputDialog("Enter Database password") ; 
		input = JOptionPane.showInputDialog("Enter the name of the database") ; 
		 
		
		TaskDatabase db = new TaskDatabase();
		 
		db.connectRootDatabase();
		if(db.doesExist(input)==true){
			
			System.out.println("Database exists");
			db.connectNewDatabase(input);
			db.launchFrame();
			
			
				
		}
		else{
		
		db.launchFrame(); // Launch the GUI	
		db.connectRootDatabase(); // Connect to the root 
		db.createDatabase(input); // Create the database
		db.createTables(input); // Close root connection, create a connection to new database, then create the tables
		Borrow.main(new String [0] ); // Run the main method from the Borrow class
		 
		
		}
		
		
		
	}
	
}
